﻿using CKS.Core.Framework;
using CKS.Core.Framework.Models;
using CKS.PLC.Core.Cache;
using CKS.PLC.Core.Event;
using CKS.Schedule;
using FreeSql;
using Infrastructure;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Net.NetworkInformation;
using System.Text;
using System.Threading.Tasks;
using ZR.Model.Models.ProductionControl;

namespace CKS.PLC.Core.CronJob
{
    
    public class SyncDataJobBase : CronJobBase
    {

        string mysqlConnTemplate = "Data Source={0};port={1};User ID={2};Password={3};Database={4};CharSet=utf8;sslmode=none;AllowPublicKeyRetrieval=True;Max Pool Size=1000;Pooling=true;";
        string sqlserverConnTemplate = "Data Source={0};User ID={1};Password={2};Initial Catalog={3};TrustServerCertificate=True;";
        
        bool showDBConfigIsNullErr = false;
        IFreeSql<MesDB> mesFsql;
        IFreeSql SourceFsql = null;
        DatabaseCollectionConfig databaseCollectionConfig = null;
        DateTime lastPingErrShowlogTime = AppCache.MinTime;
        DateTime lastErrlogTime = AppCache.MinTime;
        DatabaseCollectionConfig config = null;
      
        public SyncDataJobBase(string jobname, DatabaseCollectionConfig config) : base(jobname, "0/1 * * * * ?") //
        {
            mesFsql = InternalApp.Fsql;
            this.config = config;
        }


        public async Task DoLoopJob(CancellationToken cancellationToken)
        {
            while (IsRunning) {

                try
                {
                    await ExecuteAsync(cancellationToken);
                }
                catch (Exception )
                {
                    EventBus.AddError($"{jobName} 执行异常");
                }
                await Task.Delay(1000);
            }
            
        }

        protected override async Task ExecuteAsync(CancellationToken cancellationToken)
        {
            
            //读取ut数据库的配置，拼接数据库连接字符串,创建数据库对象
            var supplierFsql =await GetFreesql();
            if(supplierFsql == null)
            {
                return;
            }

            try
            {
                //如果能Ping通在继续
                var ipPass = await NetworkHelper.PingIPAsync(databaseCollectionConfig.Ip);
                if (ipPass == false)
                {
                    if ((DateTime.Now - lastPingErrShowlogTime).TotalHours > 2)
                    {
                        lastPingErrShowlogTime = DateTime.Now;
                        EventBus.AddError($"{jobName}数据库IP无法Ping通...");
                        return;
                    }
                }
                var dbconfig = config;
                //查询UT数据库 保存数据到动态表 
                var tableConfiges = mesFsql.Select<DatabaseCollectionTables>().Where(a => a.BusinessCode == config.BusinessCode).ToList();
                foreach (var table in tableConfiges)
                {
                   int nums = SyncTableData(table, supplierFsql);
                    if(nums>0)
                    {
                        EventBus.AddLog($"{jobName}数据表{table.TargetTableName}同步{nums}条数据");
                    }
                }
            }
            catch (Exception ex)
            {
                SourceFsql?.Dispose();
                SourceFsql = null;
                throw;
            }



            await Task.CompletedTask;
        }
        /// <summary>
        /// 从供应商数据库同步数据到指定的表
        /// </summary>
        /// <param name="table"></param>
        /// <param name="sourceFs"></param>
        /// <exception cref="NotImplementedException"></exception>
        private int SyncTableData(DatabaseCollectionTables config, IFreeSql sourceFs)
        {
            string tableName = config.TargetTableName;
            const string prefix = "cks_t_";
            if (tableName.StartsWith(prefix))
                tableName = tableName.Substring(prefix.Length);
            var table = mesFsql.Select<CksVirtualTable>().Where(a => a.TableName == tableName).First();
            if (table == null)
                return 0;
            var columns = mesFsql.Select<CksVirtualColumn>().Where(a => a.TableId == table.Id).OrderBy(a => a.Sequence).ToList();
            var targetTableInfo = ServiceCommon.GetFreesqlTableInfo(table, columns);

            //查询ut数据库，遍历目标表列头相同，赋值给object,写入到动态表中。
            string querySql;
            if (databaseCollectionConfig.Type.ToInt() == 0)
            {
                querySql = $@"SELECT * FROM `{config.SourceTableName}` 
                         WHERE `{config.SourceIdColumnName}` > @LastId 
                         ORDER BY `{config.SourceIdColumnName}` ASC 
                         LIMIT @BatchSize";
            }
            else // SQL Server
            {
                querySql = $@"SELECT TOP (@BatchSize) * FROM [{config.SourceTableName}] 
                         WHERE [{config.SourceIdColumnName}] > @LastId 
                         ORDER BY [{config.SourceIdColumnName}] ASC";
            }

            // 获取源表所有字段
            var sourceColumns = GetSourceTableColumns(sourceFs, config.SourceTableName);
            
            // 忽略大小写检查源表是否包含指定的ID字段
            bool hasSourceIdColumn = sourceColumns.Any(col =>
                string.Equals(col, config.SourceIdColumnName, StringComparison.OrdinalIgnoreCase)
            );

            if (!hasSourceIdColumn)
                throw new ArgumentException($"源表 {config.SourceTableName} 不存在ID字段：{config.SourceIdColumnName}（忽略大小写）");


            int BatchSize = 10;
            var queryParams = new
            {
                LastId = config.LatestSyncMaxId,
                BatchSize = BatchSize
            };
            var dataTable = sourceFs.Ado.ExecuteDataTable(querySql, queryParams);
            if (dataTable.Rows.Count == 0)
                return 0; // 无增量数据
            // 2. 获取本次同步的源表最大ID（用于更新记录）
            long currentMaxSourceId = dataTable.AsEnumerable()
                .Max(row => Convert.ToInt64(row[config.SourceIdColumnName]));


            List<object> inserts = new List<object>();
            foreach ( DataRow row in dataTable.Rows )
            {
                Dictionary<string, object> body = new Dictionary<string, object>();

                sourceColumns.Select(a => a != config.SourceIdColumnName);
                foreach (var tarCol in columns)
                {
                    var existsCol =sourceColumns.Find(a => a.ToLower() == tarCol.ColumnName.ToLower());
                    if (existsCol == null)
                        continue;
                    body[tarCol.ColumnName] = row[existsCol].ToString();
                }
                body["CreatedTime"] = System.DateTime.Now;
                body["Nullity"] = false;
                body["IsDeleted"] = false;
                object obj = targetTableInfo.CreateInstance(body);
                inserts.Add(obj);
                
            }
            
            mesFsql.Insert<object>().AsType(targetTableInfo.Type).AppendData(inserts).ExecuteAffrows(); //保存到动态表
            config.LastSyncTime = System.DateTime.Now;
            config.LatestSyncMaxId = currentMaxSourceId;
            mesFsql.Update<DatabaseCollectionTables>().SetSource(config)
                .UpdateColumns(a=>new { a.LastSyncTime,a.LatestSyncMaxId })
                .ExecuteAffrows();

            return inserts.Count;
        }

        public async Task<IFreeSql> GetFreesql()
        {
            try
            {
                if (SourceFsql != null)
                    return SourceFsql;

                var dbconfig = mesFsql.Select<DatabaseCollectionConfig>().Where(a=>a.BusinessCode == config.BusinessCode).First();
                if (dbconfig == null)
                {
                    if (showDBConfigIsNullErr == false)
                    {
                        showDBConfigIsNullErr = true; return null;
                        throw new Exception($"数据库配置无效！");
                    }
                }
                databaseCollectionConfig = dbconfig;

                var ipPass = await NetworkHelper.PingIPAsync(databaseCollectionConfig.Ip);
                if(!ipPass)
                {
                    if ((DateTime.Now - lastPingErrShowlogTime).TotalHours > 2)
                    {
                        lastPingErrShowlogTime = DateTime.Now;
                        EventBus.AddError($"{jobName}数据库IP无法Ping通...");
                       
                    }
                    throw new Exception($"Ping不通！");
                }
                //获取连接字符串
                string connStr = "";
                if (dbconfig.Type.ToInt() == 0)
                    connStr = string.Format(mysqlConnTemplate, dbconfig.Ip, dbconfig.Port, dbconfig.Uid, dbconfig.Pwd, dbconfig.DataBase);
                else
                    connStr = string.Format(sqlserverConnTemplate, dbconfig.Ip, dbconfig.Uid, dbconfig.Pwd, dbconfig.DataBase);

                var fsql = new FreeSqlBuilder()
                .UseConnectionString(
                    dbconfig.Type.ToInt() == 0 ? DataType.MySql : DataType.SqlServer,
                    connStr
                )
                .UseAutoSyncStructure(false) // 关闭自动建表（假设目标表已存在）
                .Build();
              // var conn = fsql.Ado.MasterPool.Get().Value;
               // conn.Open();
                if (fsql.Ado.ExecuteConnectTest() == false)
                    throw new Exception($"{jobName}数据库连接失败！");

                EventBus.AddLog($"{jobName}数据库连接成功");
                SourceFsql = fsql;
                return SourceFsql;
            }
            catch (Exception ex)
            {
                if ((DateTime.Now - lastErrlogTime).TotalMinutes > 2)
                {
                    lastErrlogTime = DateTime.Now;
                    EventBus.AddError($"{jobName}:{ex.Message}");
                }
                SourceFsql = null;
                return SourceFsql;
            }

        }


      







    }
}
